package mumethod;

import java.io.File;
import java.io.IOException;
import java.sql.Date;
import java.util.Calendar;
import java.util.List;

import mudbconnection.DBQuestionnaires;
import muobject.Question;
import muobject.Questionnaire;

import org.eclipse.swt.SWT;
import org.eclipse.swt.widgets.Table;
import org.eclipse.swt.widgets.TableItem;
import org.springframework.beans.factory.annotation.Autowired;

import jxl.Sheet;
import jxl.Workbook;
import jxl.format.CellFormat;
import jxl.read.biff.BiffException;

public class ImportFunction {

	private DateControl dateControl;
	
	public ImportFunction(){
		dateControl = new LibFunction();
	}
	
	public void ImportStudentExcel(String path, Table table) {
		try {
			Workbook workbook = Workbook.getWorkbook(new File(path));
			Sheet sheet = workbook.getSheet(0);
			DateControl dateControl = new LibFunction();
			for (int i = 0; i < sheet.getRows(); i++) {
				if (!sheet.getCell(0, i).getContents().equals("入學編號")) {
					if (!sheet.getCell(0, i).getContents().equals("")) {
						TableItem item = new TableItem(table, SWT.NULL);
						item.setText(0, sheet.getCell(0, i).getContents());
						item.setText(1, sheet.getCell(1, i).getContents());
						item.setText(2, sheet.getCell(2, i).getContents());
						item.setText(3, sheet.getCell(3, i).getContents());
						item.setText(4, sheet.getCell(4, i).getContents());
						item.setText(5, sheet.getCell(5, i).getContents());
						item.setText(6, sheet.getCell(6, i).getContents());
						item.setText(7, dateControl.convertDate(sheet.getCell(
								7, i).getContents()) == "" ? "格式錯誤" : dateControl.convertDate(sheet.getCell(
										7, i).getContents()));
					}

				}
			}
			workbook.close();
		} catch (Exception e) {

		}
	}
	
	public String ImportPhysicalExcel(String path, Table table, String currentYear) {
		int row = 1;
		try {
			Workbook workbook = Workbook.getWorkbook(new File(path));
			Sheet sheet = workbook.getSheet(0);
			String testDate;
			if (!sheet.getCell(0, 0).getContents().equals("測試日期")) 
				return "標題格式錯誤";
			for (int i = 0; i < sheet.getRows(); i++) {
				if (!sheet.getCell(0, i).getContents().equals("測試日期")) {
					if (!sheet.getCell(0, i).getContents().equals("")) {
						testDate = dateControl.convertDate(sheet.getCell(
								0, i).getContents()) == "" ? "格式錯誤" : dateControl.convertDate(sheet.getCell(
										0, i).getContents());
						if(testDate.equals("格式錯誤"))
							return (i+1) + "行  格式錯誤";
						else if(dateControl.getDataYear(dateControl.getStringDate(testDate)).equals(currentYear)){
							TableItem item = new TableItem(table, SWT.NULL);
							item.setText(1, testDate);
							item.setText(2, sheet.getCell(1, i).getContents());
							item.setText(3, sheet.getCell(2, i).getContents());
							item.setText(4, sheet.getCell(3, i).getContents());
							item.setText(5, sheet.getCell(4, i).getContents());
							item.setText(6, sheet.getCell(5, i).getContents());
							item.setText(7, sheet.getCell(6, i).getContents());
							item.setText(8, sheet.getCell(7, i).getContents());
							item.setText(9, sheet.getCell(8, i).getContents());
							item.setText(0, sheet.getCell(9, i).getContents());
							item.setText(11, sheet.getCell(10, i).getContents());
							item.setText(12, sheet.getCell(11, i).getContents());
							item.setText(13, sheet.getCell(12, i).getContents());
							item.setText(14, sheet.getCell(13, i).getContents());
							item.setText(15, sheet.getCell(14, i).getContents());
							item.setText(16, sheet.getCell(15, i).getContents());
						}else{
							return (i+1) + "行  資料非本學年";
						}
					}
				}
				row ++;
			}
			workbook.close();
			return "";
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return row + "行  格式錯誤";
		}
	}
	
	public String ImportQuestionnaireExcel(String path, String currentYear, List<Question> questions, AccessQuestionnaires getQuestionnaires) {
		int row = 1;
		try {
			Workbook workbook = Workbook.getWorkbook(new File(path));
			Sheet sheet = workbook.getSheet(0);
			String testDateString, stno, year, month, qno;
			Date testDate;
			Questionnaire questionnaire;
			if (!sheet.getCell(6, 0).getContents().equals("測試日期")) 
				return "標題格式錯誤";
			for (int i = 0; i < sheet.getRows(); i++) {
				if (!sheet.getCell(6, i).getContents().equals("") & !sheet.getCell(6, i).getContents().equals("測試日期")) {
					testDateString = sheet.getCell(6, i).getContents();
					testDateString = dateControl.convertDate(sheet.getCell(
							6, i).getContents()) == "" ? "格式錯誤" : dateControl.convertDate(sheet.getCell(
									6, i).getContents());
					if(testDateString.equals("格式錯誤")){
						return (i+1) + "行  格式錯誤";
					}else if(dateControl.getDataYear(dateControl.getStringDate(testDateString)).equals(currentYear)){
						testDate =  Date.valueOf(testDateString);
						stno = sheet.getCell(5, i).getContents();
						year = dateControl.getDataYear(testDate);
						month = dateControl.getDateType(testDate, "month");
						int qi = 7;
						for(Question q : questions){
							qno = q.getQno();
							questionnaire = new Questionnaire(0, stno, year, month, 1, testDate, qno, sheet.getCell(qi, i).getContents(), "false", null, null);
							if(getQuestionnaires.getAnswer(stno, year, month, testDateString, qno).equals("")){
								getQuestionnaires.setQuestionnaire(questionnaire);
							}else{
								getQuestionnaires.updateQuestionnaire(questionnaire);
							}
							qi++;
						}
					}else{
						return (i+1) + "行  資料非本學年";
					}
					row ++;
				}
			}
			workbook.close();
			return "";
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return row + "行  格式錯誤";
		}
	}
}
